﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using Entities;
using System.Linq;
using WebLog;
namespace Data
{
    public sealed class VotingData
    {
        public bool DeleteCandidate(Candidate cc)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("ELIMINAR_CANDIDATO");
                bd.AssignInt("@Id_candidato", Convert.ToInt32(cc.id));
                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("ELIMINAR_TABLA_CANDIDATO");
                bd.AssignString("@nombre_Tabla", cc.nameSQLTable);
                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("ELIMINAR_PROCEDURE_CANDIDATO");
                bd.AssignString("@nombre_Procedure", cc.nameSQLProcedure);
                bd.ExecuteNonQuery();

                bd.CommitTransaction();
                response = true;
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return response;
        }

        public bool UpdateCandidate(Candidate cc, string nameTable, string nameProcedure)
        {
            DataBase bd = new DataBase();

            bool response = false;
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("ACTUALIZAR_CANDIDATO");
                bd.AssignInt("@Id_candidato", Convert.ToInt32(cc.id));
                bd.AssignString("@Nombre_candidato", cc.name);
                bd.AssignString("@Segundo_Nombre_candidato", cc.middleName);
                bd.AssignString("@Apellido_candidato", cc.lastName);
                bd.AssignString("@Segundo_Apellido_candidato", cc.secondLastName);
                bd.AssignString("@Nombre_Tabla_candidato", cc.nameSQLTable);
                bd.AssignString("@Cargo_candidato", cc.office);
                bd.AssignString("@nombre_Fotografia_candidato", cc.pictureString);
                bd.AssignString("@Nombre_Procedure_candidato", cc.nameSQLProcedure);
                bd.AssignString("@Email_candidato", cc.email);
                bd.AssignString("@Telefono_candidato", cc.telephone);
                bd.AssignString("@Celular_candidato", cc.cellPhone);
                bd.AssignInt("@id_votacion", cc.votingId);

                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("ELIMINAR_TABLA_CANDIDATO");
                bd.AssignString("@nombre_Tabla", nameTable);
                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("ELIMINAR_PROCEDURE_CANDIDATO");
                bd.AssignString("@nombre_Procedure", nameProcedure);
                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("Crear_Tabla_Candidato");
                bd.AssignString("@NombreTabla_candidato", cc.nameSQLTable);
                bd.ExecuteNonQuery();

                bd.ProcedureSqlName("Crear_Procedure_Candidato");
                bd.AssignString("@nombre_Tabla", cc.nameSQLTable);
                bd.AssignString("@nombre_Procedure", cc.nameSQLProcedure);
                bd.ExecuteNonQuery();

                bd.CommitTransaction();
                response = true;

            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return response;

        }

        public bool InsertCandidate(List<Candidate> list, Voter i)
        {
            DataBase bd = new DataBase();
            bool response = false;
            int negative = 0;
            DateTime dt = DateTime.Now;

            try
            {
                bd.Connect();
                bd.BeginTransaction();

                foreach (Candidate cc in list)
                {
                    if (string.IsNullOrWhiteSpace(cc.id))
                    {
                        bd.ProcedureSqlName("INSERTAR_CANDIDATO_BLANCO");
                    }
                    else
                    {
                        bd.ProcedureSqlName(cc.nameSQLProcedure);
                    }
                    bd.AssignDateTime("@FECHA_VOTACION", dt);
                    bd.AssignInt("@Voto", 1);
                    bd.AssignInt("@id_votacion", cc.votingId);
                    int escalar = bd.ExecuteNonQuery();
                    if (escalar <= 0)
                    {
                        negative++;
                        break;
                    }
                }

                if (negative == 0)
                {
                    int votingId = list.First<Candidate>().votingId;
                    bd.ProcedureSqlName("INSERTAR_COMPROBANTE_VOTANTE");
                    bd.AssignInt("@ID_VOTACION", votingId);
                    bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(i.id));
                    bd.AssignString("@ID_COMPROBANTE", i.voucherVotingId);
                    bd.AssignString("@PATH_COMPROBANTE", i.picturePath);

                    int resultado = bd.ExecuteNonQuery();
                    if (resultado > 0)
                    {
                        bd.ProcedureSqlName("ELIMINAR_RELACION VOTANTE_VOTACION");
                        bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(i.id));
                        bd.AssignInt("@ID_VOTACION", votingId);
                        int resultadodos = bd.ExecuteNonQuery();

                        if (resultadodos > 0)
                        {
                            bd.CommitTransaction();
                            response = true;
                        }
                    }

                    else
                    {
                        bd.RollbackTransaction();
                        response = false;
                    }
                }
                else
                {
                    bd.RollbackTransaction();
                    response = false;
                }

            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return response;

        }

        public bool InsertCandidateWhiteOrNull(string procedure, int votingId)
        {
            DataBase bd = new DataBase();
            {
                bool response = false;
                DateTime dt = DateTime.Now;
                try
                {
                    bd.Connect();
                    bd.BeginTransaction();
                    bd.ProcedureSqlName(procedure);
                    bd.AssignDateTime("@FechaVotacion", dt);
                    bd.AssignInt("@Voto", 1);
                    bd.AssignInt("@id_votacion", votingId);
                    byte escalar = (byte)bd.ExecuteNonQuery();

                    if (escalar > 0)
                    {
                        bd.CommitTransaction();
                        response = true;
                    }
                }
                catch (Exception e)
                {
                    Logger logger = Logger.GetInstance;
                    logger.WriteLog(e);
                    bd.RollbackTransaction();
                    response = false;
                }
                finally
                {
                    bd.Disconnect();
                    bd.ReleaseResources();
                }
                return response;
            }
        }

        public List<Candidate> GetListCandidates(int votingId)
        {
            DataBase bd = new DataBase();
            List<Candidate> list = new List<Candidate>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("Obtener_Candidatos");
                bd.AssignInt("@ID_VOTACION", votingId);
                list = LoadListOfCandidates(bd.ExecuteDataReader());
            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return list;
        }

        public List<string> GetListCandidatesOffices()
        {
            List<string> listaCargos = new List<string>();
            DataBase bd = new DataBase();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_CARGOS_CANDIDATO");
                readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        listaCargos.Add(readerDataSource.GetString(0));
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    listaCargos = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                listaCargos = null;
            }
            finally
            {

                bd.Disconnect();
                bd.ReleaseResources();
            }
            return listaCargos;
        }

        public Candidate GetCandidateById(string id)
        {
            Candidate c = null;
            DataBase bd = new DataBase();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_CANDIDATO_POR_ID");
                bd.AssignInt("@Id_candidato", Convert.ToInt32(id));
                readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        c = new Candidate();
                        c.id = readerDataSource.GetInt32(0).ToString();
                        c.name = readerDataSource.GetString(1);
                        c.middleName = readerDataSource.GetString(2);
                        c.lastName = readerDataSource.GetString(3);
                        c.secondLastName = readerDataSource.GetString(4);
                        c.number = readerDataSource.GetInt32(5);
                        c.nameSQLTable = readerDataSource.GetString(6);
                        c.office = readerDataSource.GetString(7);
                        c.pictureString = readerDataSource.GetString(8);
                        c.nameSQLProcedure = readerDataSource.GetString(9);
                        c.email = readerDataSource.GetString(10);
                        c.telephone = readerDataSource.GetString(11);
                        c.cellPhone = readerDataSource.GetString(12);
                        c.registrationDate = readerDataSource.GetDateTime(13);
                        c.votingId = readerDataSource.GetInt32(14);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    c = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                c = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return c;
        }

        public List<VoucherVoting> GetListVoucherVoting(string id)
        {
            List<VoucherVoting> list = new List<VoucherVoting>(); ;
            DataBase bd = new DataBase();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_COMPROBANTE_VOTANTE_VOTACION");
                bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(id));
                readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        VoucherVoting c = new VoucherVoting();
                        c.id = readerDataSource.GetInt32(0).ToString();
                        c.votingId = readerDataSource.GetInt32(1).ToString();
                        c.voterId = readerDataSource.GetInt32(2).ToString();
                        c.idPdf = readerDataSource.GetString(3);
                        c.voucherPath = readerDataSource.GetString(4);
                        c.votingName = readerDataSource.GetString(5);
                        list.Add(c);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public bool InsertVoting(Voting v, List<Candidate> list, List<Group> groups)
        {
            bool response = false;
            int positivo = 0;
            int votingId = 0;
            DataBase bd = new DataBase();
            //id de todos los votantes correspondientes a los grupos marcados
            List<int> voterID = new List<int>();
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("INSERTAR_VOTACION");
                bd.AssignString("@nombre_votacion", v.name);
                bd.AssignDateTime("@inicio_votacion", v.votingDateStart);
                bd.AssignDateTime("@termino_votacion", v.votingDateEnd);
                bd.AssignString("@hora_inicio_votacion", v.votingStartTime);
                bd.AssignString("@hora_termino_votacion", v.votingEndTime);
                bd.AssignBool("@bloqueado_votacion", v.votingBlocked);
                bd.AssignBool("@votacion_activa", v.votingActive);
                votingId = bd.ExecuteScalar();

                if (votingId > 0)
                {
                    foreach (Candidate c in list)
                    {
                        bd.ProcedureSqlName("INSERTAR_CANDIDATO");
                        bd.AssignString("@Nombre_candidato", c.name);
                        bd.AssignString("@Segundo_Nombre_candidato", c.middleName);
                        bd.AssignString("@Apellido_candidato", c.lastName);
                        bd.AssignString("@Segundo_Apellido_candidato", c.secondLastName);
                        bd.AssignInt("@Numero_candidato", c.number);
                        bd.AssignString("@Nombre_Tabla_candidato", c.nameSQLTable);
                        bd.AssignString("@Cargo_candidato", c.office);
                        bd.AssignString("@Nombre_Fotografia_candidato", c.pictureString);
                        bd.AssignString("@Nombre_Procedure_candidato", c.nameSQLProcedure);
                        bd.AssignString("@Email_candidato", c.email);
                        bd.AssignString("@Telefono_candidato", c.telephone);
                        bd.AssignString("@Celular_candidato", c.cellPhone);
                        bd.AssignDateTime("@fecha_Inscripcion_candidato", c.registrationDate);
                        bd.AssignInt("@id_votacion", votingId);
                        int insertarUsuario = bd.ExecuteNonQuery();

                        if (insertarUsuario > 0)
                        {
                            positivo++;
                        }

                        bd.ProcedureSqlName("CREAR_TABLA_CANDIDATO");
                        bd.AssignString("@NombreTabla_candidato", c.nameSQLTable);
                        bd.ExecuteNonQuery();

                        bd.ProcedureSqlName("CREAR_PROCEDURE_CANDIDATO");
                        bd.AssignString("@nombre_Tabla", c.nameSQLTable);
                        bd.AssignString("@nombre_Procedure", c.nameSQLProcedure);
                        bd.ExecuteNonQuery();

                    }

                    if (positivo == list.Count)
                    {
                        foreach (Group g in groups)
                        {
                            bd.ProcedureSqlName("OBTENER_VOTANTES_POR_GRUPO");
                            bd.AssignInt("@ID_GRUPO", Convert.ToInt32(g.id));
                            DbDataReader result = bd.ExecuteDataReader();
                            //recupero todos los id de votantes por cada grupo
                            foreach (DbDataRecord id in result)
                            {
                                voterID.Add(id.GetInt32(0));
                            }
                            result.Close();
                        }
                        bool assingVoting = false;

                        foreach (int voter_id in voterID)
                        {
                            bd.ProcedureSqlName("INSERTAR_VOTANTE_VOTACION");
                            bd.AssignInt("@ID_VOTANTE", voter_id);
                            bd.AssignInt("@ID_VOTACION", votingId);

                            int resp = bd.ExecuteScalar();
                            if (resp > 0)
                            {
                                assingVoting = true;
                            }
                            else
                            {
                                assingVoting = false;
                                break;
                            }
                        }
                        if (assingVoting)
                        {
                            // ctm.. finalmente inserto en la tabla grupo_votacions los registros
                            bool assingRelation = false;
                            foreach (Group gr in groups)
                            {
                                bd.ProcedureSqlName("INSERTAR_GRUPO_VOTACION");
                                bd.AssignInt("@ID_GRUPO", Convert.ToInt32(gr.id));
                                bd.AssignInt("@ID_VOTACION", votingId);
                                int respo = bd.ExecuteScalar();
                                if (respo > 0)
                                {
                                    assingRelation = true;
                                }
                                else
                                {
                                    assingRelation = false;
                                    break;
                                }
                            }

                            if (assingRelation)
                            {
                                bd.CommitTransaction();
                                response = true;
                            }
                            else
                            {
                                bd.RollbackTransaction();
                                response = false;
                            }
                        }
                        else
                        {
                            bd.RollbackTransaction();
                            response = false;
                        }
                    }
                    else
                    {
                        bd.RollbackTransaction();
                        response = false;
                    }
                }
                else
                {
                    bd.RollbackTransaction();
                    response = false;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public List<Voting> GetListVoting()
        {
            List<Voting> listaVota = new List<Voting>();
            DataBase bd = new DataBase();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTACION");
                listaVota = LoadListOfVoting(bd.ExecuteDataReader());
                foreach (Voting v in listaVota)
                {
                    v.groups = GetListGroupByVotingId(Convert.ToInt32(v.id));
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                listaVota = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }

            return listaVota;
        }

        public List<Group> GetListGroupByVotingId(int votingId)
        {
            List<Group> list = new List<Group>();
            DataBase bd = new DataBase();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_GRUPOS_POR_VOTACION");
                bd.AssignInt("@IDVOTACION", votingId);
                var resp = bd.ExecuteDataReader();

                foreach (DbDataRecord row in resp)
                {
                    list.Add(new Group { id = row.GetInt32(0).ToString(), name = row.GetString(1) });
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            return list;

        }

        public List<Voting> GetListActiveVotingByVoterId(int voterId)
        {
            List<Voting> listaVota = new List<Voting>();
            DataBase bd = new DataBase();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTACION_ACTIVA_VOTANTE");
                bd.AssignInt("@id_votante", voterId);
                listaVota = LoadListOfVoting(bd.ExecuteDataReader());

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                listaVota = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }

            return listaVota;
        }

        //trae todas las votaciones que no estaen bloqueadas
        public List<Voting> GetListNoBlockedVotingByVoterId(int voterId)
        {
            List<Voting> listaVota = new List<Voting>();
            DataBase bd = new DataBase();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTACION_NOBLOQUEADA_VOTANTE");
                bd.AssignInt("@id_votante", voterId);
                listaVota = LoadListOfVoting(bd.ExecuteDataReader());

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                listaVota = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }

            return listaVota;
        }

        public Voting GetVotingById(int id)
        {
            Voting v = null;
            DataBase bd = new DataBase();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTACION_POR_ID");
                bd.AssignInt("@ID_VOTACION", id);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        v = new Voting();
                        v.id = readerDataSource.GetInt32(0).ToString();
                        v.name = readerDataSource.GetString(1);
                        v.votingDateStart = readerDataSource.GetDateTime(2);
                        v.votingDateEnd = readerDataSource.GetDateTime(3);
                        v.votingStartTime = readerDataSource.GetString(4);
                        v.votingEndTime = readerDataSource.GetString(5);
                        v.votingBlocked = readerDataSource.GetBoolean(6);
                        v.votingActive = readerDataSource.GetBoolean(7);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    v = null;
                }
            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                v = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }

            return v;
        }

        private List<Voting> LoadListOfVoting(DbDataReader datosDb)
        {
            List<Voting> list = new List<Voting>();
            DbDataReader readerDataSource = datosDb;
            try
            {
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        Voting v = new Voting();
                        v.id = readerDataSource.GetInt32(0).ToString();
                        v.name = readerDataSource.GetString(1);
                        v.votingDateStart = readerDataSource.GetDateTime(2);
                        v.votingDateEnd = readerDataSource.GetDateTime(3);
                        v.votingStartTime = readerDataSource.GetString(4);
                        v.votingEndTime = readerDataSource.GetString(5);
                        v.votingBlocked = readerDataSource.GetBoolean(6);
                        v.votingActive = readerDataSource.GetBoolean(7);
                        v.votingBlockedView = (v.votingBlocked) ? "Si" : "No";
                        v.votingActiveView = (v.votingActive) ? "Si" : "No";
                        list.Add(v);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            return list;
        }

        private List<Candidate> LoadListOfCandidates(DbDataReader datosDb)
        {
            List<Candidate> list = new List<Candidate>();
            DbDataReader readerDataSource = datosDb;
            try
            {
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        Candidate c = new Candidate();
                        c.id = readerDataSource.GetInt32(0).ToString();
                        c.name = readerDataSource.GetString(1);
                        c.middleName = readerDataSource.GetString(2);
                        c.lastName = readerDataSource.GetString(3);
                        c.secondLastName = readerDataSource.GetString(4);
                        c.number = readerDataSource.GetInt32(5);
                        c.nameSQLTable = readerDataSource.GetString(6);
                        c.office = readerDataSource.GetString(7);
                        c.pictureString = readerDataSource.GetString(8);
                        c.nameSQLProcedure = readerDataSource.GetString(9);
                        c.email = readerDataSource.GetString(10);
                        c.telephone = readerDataSource.GetString(11);
                        c.cellPhone = readerDataSource.GetString(12);
                        c.registrationDate = readerDataSource.GetDateTime(13);
                        c.votingId = readerDataSource.GetInt32(14);
                        list.Add(c);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            return list;
        }
    }
}
